#!/usr/bin/python
# -*- coding: utf-8 -*-
# @Time    : 2020/6/15 15:41
# @Author  : JY.Liu
# @Site    : http://github.com/lh1993
# @Mail    : lhln0119@163.com
# @File    : db_exec.py
# @Software: PyCharm

from tools import get_config
from decimal import Decimal
from datetime import date
import pymysql
import json


class DB(object):

    def __init__(self, host, username, password, dbname):
        self.host = host
        self.username = username
        self.password = password
        self.dbname = dbname

    def default(self, obj):
        if isinstance(obj, Decimal):
            return str(obj)
        elif isinstance(obj, date):
            return str(obj)
        raise TypeError(
            "Object of type '%s' is not JSON serializable" %
            type(obj).__name__)

    def get_data(self, sql_data):
        """查询结果json序列化"""
        data_list = []
        for data in sql_data:
            data_list.append(list(data))
        return json.dumps(data_list, default=self.default)

    def execute(self, sql):
        """获取sql查询结果"""
        try:
            db_connect = pymysql.connect(
                db=self.dbname,
                host=self.host,
                user=self.username,
                passwd=self.password,
                charset="utf8")
            db_cursor = db_connect.cursor()
            db_cursor.execute(sql)
            row = db_cursor.fetchall()
            data = self.get_data(row)
        except Exception as e:
            print(e)
            return json.dumps([])
        return data

    def execute_add_or_update(self, sql):
        """获取sql查询结果"""
        try:
            db_connect = pymysql.connect(
                db=self.dbname,
                host=self.host,
                user=self.username,
                passwd=self.password,
                charset="utf8")
            db_cursor = db_connect.cursor()
            db_cursor.execute(sql)
            # 涉及写操作要注意提交
            db_connect.commit()
            # 关闭连接
            db_cursor.close()
            db_connect.close()
        except Exception as e:
            print(e)


async def save_sls_alert(sql):
    host = get_config.host
    username = get_config.user
    password = get_config.passwd
    dbname = get_config.dbname
    db = DB(host=host, username=username, password=password, dbname=dbname)
    db.execute_add_or_update(sql=sql)
